﻿
CREATE PROCEDURE [dbo].[CatalogDelete]
(
	@Original_Id int,
	@Original_MD datetime,
	@MW varchar(50) -- если = PhysicallyDelete то запись удаляется физически. В противном случае поле ToDelete устанавливается в 1
)
AS
DECLARE @tblCatTree TABLE (
Id int NOT NULL, 
CatLevel int NOT NULL
)
DECLARE @strMsg nvarchar(500), @intRet int, @intErrCode int
DECLARE @intLineId int, @datLineMD datetime
DECLARE @datMD datetime, @intDepartmentId int, @bitPhysicallyDelete bit

	SET NOCOUNT ON;

SELECT @intRet = Id
FROM CO.Catalog WHERE Id = @Original_Id AND MD = @Original_MD
IF (@@rowcount <> 1)
BEGIN
	SELECT @intErrCode = 1, 
	@strMsg = 'Данные изменены после последнего запроса!'
	RAISERROR (@strMsg, 11, 1)
	RETURN @intErrCode
END

IF (@Original_Id = 1) OR (@Original_Id = 0)
BEGIN
	SELECT @intErrCode = 2, 
	@strMsg = 'Нельзя удалять категорию с идентефикатором 0 или 1!'
	RAISERROR (@strMsg, 11, 1)
	RETURN @intErrCode
END

--Проверка возможности физического удаления записи из таблиц справочников
EXEC @intRet = dbo.CheckDictionaryDelete @bitPhysicallyDelete OUTPUT, @intDepartmentId OUTPUT, @MW OUTPUT
IF @intRet <> 0 RETURN @intRet

SET @intRet = 0 ;

--удаление дочерних категорий
	WITH ThisCatalog(Id, ParentID, CatLevel) AS 
	(
		SELECT Id, ParentID, 0 AS CatLevel
		FROM CO.Catalog
		WHERE Id = @Original_Id
		UNION ALL
		SELECT C.Id, C.ParentID, CatLevel + 1
		FROM CO.Catalog C INNER JOIN ThisCatalog P ON C.ParentID = P.Id 
	)

	INSERT INTO @tblCatTree (Id, CatLevel)
	SELECT Id, CatLevel FROM ThisCatalog --ORDER BY CatLevel DESC

--удаление препаратов
DECLARE AssortmentForCatalog CURSOR FAST_FORWARD FOR
SELECT A.Id, A.MD FROM [CA].[Assortment] AS A INNER JOIN @tblCatTree S ON A.CatalogId = S.Id

OPEN AssortmentForCatalog

FETCH NEXT FROM AssortmentForCatalog INTO @intLineId, @datLineMD

BEGIN TRAN

WHILE (@@FETCH_STATUS = 0)
BEGIN
		EXEC @intRet = dbo.AssortmentDelete @intLineId, @datLineMD, @MW
	
		SET @intErrCode = @@error
		IF @intErrCode <> 0
		BEGIN 
			IF @@trancount > 0 ROLLBACK TRANSACTION
			CLOSE AssortmentForCatalog
			DEALLOCATE AssortmentForCatalog
			RETURN @intErrCode
		END
		
		IF @intRet <> 0
		BEGIN 
			IF @@trancount > 0 ROLLBACK TRANSACTION
			CLOSE AssortmentForCatalog
			DEALLOCATE AssortmentForCatalog
			RETURN @intRet
		END
	
	FETCH NEXT FROM AssortmentForCatalog INTO @intLineId, @datLineMD
END

CLOSE AssortmentForCatalog
DEALLOCATE AssortmentForCatalog

EXEC [dbo].[ProcessStatistic] Null, @datMD OUTPUT, @MW OUTPUT
	SET NOCOUNT OFF;
IF @intDepartmentId <> 0 --в поздразделениях запись всегда физически удаляется
	DELETE D FROM CO.Catalog D INNER JOIN @tblCatTree S ON S.Id = D.Id
ELSE
BEGIN
	IF @bitPhysicallyDelete = 1
		DELETE D FROM CO.Catalog D INNER JOIN @tblCatTree S ON S.Id = D.Id
	ELSE --Обычные пользователи только помечают запись для удаления
		UPDATE D SET 
		ToDelete = 1,
		[MD] = @datMD, [MW] = @MW
		FROM CO.Catalog D INNER JOIN @tblCatTree S ON S.Id = D.Id
END

SET @intErrCode = @@error
IF @intErrCode <> 0
BEGIN 
	IF @@trancount > 0 ROLLBACK TRANSACTION
	RETURN @intErrCode
END

COMMIT TRAN

RETURN 0



